using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using CarlosAg.ExcelXmlWriter;
using C5;

namespace CoreCalc.IO
{
    class Excel2003XMLReader
    {
        public static Workbook ReadFile(string filename)
        {
            Workbook backWorkbook = new Workbook();
            CarlosAg.ExcelXmlWriter.Workbook workbook = new CarlosAg.ExcelXmlWriter.Workbook();
            if (!System.IO.File.Exists(filename))
                throw new FileNotFoundException("The file was not found: " + filename);
            
            workbook.Load(filename);

            foreach (CarlosAg.ExcelXmlWriter.Worksheet worksheet in workbook.Worksheets)
            {
                if (worksheet.Table.ExpandedColumnCount > 0 && worksheet.Table.ExpandedRowCount > 0)
                    new Sheet(backWorkbook, worksheet.Name, worksheet.Table.ExpandedColumnCount, worksheet.Table.ExpandedRowCount, false);
            }
            HashDictionary<string, Expr> expressionIndex = new HashDictionary<string, Expr>();

            foreach (CarlosAg.ExcelXmlWriter.Worksheet worksheet in workbook.Worksheets)
            {
                if (worksheet.Table.ExpandedColumnCount > 0 && worksheet.Table.ExpandedRowCount > 0)
                {
                    int rowIndex = -1;
                    Sheet newSheet = backWorkbook[worksheet.Name];
                    for (int r = 0; r < worksheet.Table.Rows.Count; r++)
                    {
                        int columnNumber = -1;
                        WorksheetRow row = worksheet.Table.Rows[r];
                        rowIndex = row.Index == 0 ? rowIndex+1 : row.Index-1;
                        for (int c = 0; c < row.Cells.Count; c++)
                        {
                            Cell newCell = null;
                            if (row.Cells[c].Formula != null)
                            {
                              Expr known;
                              string formula = row.Cells[c].Formula;
                              if (expressionIndex.Find(ref formula, out known))
                              {
                                Formula form = new Formula(backWorkbook, known);
                                newCell = form;
                              }
                              else
                              {
                                newCell = Cell.Parse(row.Cells[c].Formula, backWorkbook, c, r);
                                Formula form = newCell as Formula;
                                if (form != null)
                                  expressionIndex.Add(row.Cells[c].Formula, form.Expr);
                              }
                            }
                            else
                            {
                              if (row.Cells[c].Data.Text != null)
                              {
                                if (row.Cells[c].Data.Type == DataType.String)
                                  newCell = Cell.Parse("'" + row.Cells[c].Data.Text, backWorkbook, c, r);
                                else if (row.Cells[c].Data.Type == DataType.Error)
                                  newCell = Cell.Parse("'" + row.Cells[c].Data.Text, backWorkbook, c, r);
                                else if (row.Cells[c].Data.Type == DataType.Number || row.Cells[c].Data.Type == DataType.Integer)
                                {
                                  newCell = new NumberCell(Convert.ToDouble(row.Cells[c].Data.Text));
                                }
                                else
                                  newCell = Cell.Parse(row.Cells[c].Data.Text, backWorkbook, c, r);
                              }
                            }
                            columnNumber = row.Cells[c].Index == 0 ? columnNumber+1 : row.Cells[c].Index-1;
                            
                            newSheet[columnNumber, rowIndex] = newCell;
                        }
                    }
                }
            }
            return backWorkbook;
        }
    }
}